--Function of adding a business unit
create or replace procedure meta_add_unit(
    --Unit id
    a_unit_id raw,
    --Unit name
    a_unit_name nvarchar2)
as
    --Row count
    a_row_count number;
begin
    --Searching for the unit with the same id
    select count(*) into a_row_count
    from meta_units
    where unit_id = a_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20031,
                'A unit with the specified id was already added (unit_id = '
                || a_unit_id
                || ') (#error_code = 20031)');
    end if;
    
    --Searching for the unit with the same name
     select count(*) into a_row_count
    from meta_units
    where unit_name = a_unit_name;
    
    if a_row_count > 0 then
        raise_application_error(-20017,
                'A unit with the specified name was already added (unit_name = '
                || a_unit_name
                || ') (#error_code = 20017)');
    end if;

    --Updating the metadata
    insert into meta_units(unit_id, unit_name)
    values(a_unit_id, a_unit_name);
end;

--go

--Function of checking unit existence
create or replace procedure p_check_unit_exists(
    --Unit id
    a_unit_id raw)
as
    --Row count
    a_row_count number := 0;
begin
    --Searching for the unit
    select count(*) into a_row_count
    from meta_units
    where unit_id = a_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20032,
                'A unit with the specified id was not found (unit_id = '
                || a_unit_id
                || ') (#error_code = 20032)');
    end if;
end;

--go

--Function of deleting a business unit
create or replace procedure meta_delete_unit(
    --Unit id
    a_unit_id raw)
as
    --Affected unit users
    a_affected_users meta_name_list;
begin
    p_check_unit_exists(a_unit_id);
    
    --Getting affected old users
    select distinct user_name
    bulk collect into a_affected_users
    from meta_user_units
    --Getting affected unit users
    where unit_id in (select * from table(p_get_unit_parents_children(a_unit_id)));
    
    --References to and from unit will be deleted on cascade
    
    --Deleting the unit
    delete from meta_units
    where unit_id = a_unit_id;
    
    --Refreshing old users acl
    for u in (select column_value user_name from table(a_affected_users)) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go

--Function of getting unit parents
create or replace function p_get_unit_parents(
    --Unit id
    a_unit_id raw)
return meta_id_list 
as
    --Parent units id list
    result meta_id_list := meta_id_list();
begin
     select distinct *
     bulk collect into result
     from (
         select parent_unit_id
         from meta_unit_parents
         start with unit_id = a_unit_id
         connect by prior parent_unit_id = unit_id
         union
         select a_unit_id from dual);

     return result;
end;

--go

--Function of getting unit children
create or replace function p_get_unit_children(
    --Unit id
    a_unit_id raw)
return meta_id_list
as
    --Child units id list
    result meta_id_list := meta_id_list();
begin
     select distinct *
     bulk collect into result
     from (
         select unit_id
         from meta_unit_parents
         start with parent_unit_id = a_unit_id
         connect by prior unit_id = parent_unit_id
         union
         select a_unit_id from dual);
     
     return result;
end;

--go

--Function of getting unit children and parents
create or replace function p_get_unit_parents_children(
    --Unit id
    a_unit_id raw)
return meta_id_list
as
    --Result units id list
    result meta_id_list := meta_id_list();
begin
     select distinct unit_id
     bulk collect into result
     from (     
        --The children
        select unit_id
        from meta_unit_parents
        start with parent_unit_id = a_unit_id
        connect by prior unit_id = parent_unit_id
        union
        --The parents
        select parent_unit_id
        from meta_unit_parents
        start with unit_id = a_unit_id
        connect by prior parent_unit_id = unit_id
        union
        select a_unit_id from dual);

     return result;
end;

--go

--Function of adding unit parent
create or replace procedure meta_add_unit_parent(
    --Unit id
    a_unit_id raw,
    --Parent unit id
    a_parent_unit_id raw)
as
    --Row count
    a_row_count number;
    --Unit id list
    a_unit_id_list meta_id_list;
begin
    p_check_unit_exists(a_unit_id);
    p_check_unit_exists(a_parent_unit_id);
    
    --Searching for the same unit parent
    select count(*) into a_row_count
    from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
    
    if a_row_count > 0 then
        raise_application_error(-20050,
                'Specified parent unit was already added (unit_id = '
                || a_unit_id || ', parent_unit_id = '
                || a_parent_unit_id
                || ') (#error_code = 20050)');
    end if;
           
    --Checking that there will be no cycle
    a_unit_id_list := p_get_unit_parents(a_parent_unit_id);
    
    for i in 1..a_unit_id_list.count loop
        if a_unit_id_list(i) = a_unit_id then
            raise_application_error(-20033,
                'Building a cycle of units is forbidden (#error_code = 20033)');
        end if;            
    end loop;
    
    --Updating the metadata
    insert into meta_unit_parents (unit_id, parent_unit_id)
    values (a_unit_id, a_parent_unit_id);
    
    --Refreshing new users acl
    for u in (select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from table(p_get_unit_parents_children(a_unit_id)))) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go

--Function of deleting unit parent
create or replace procedure meta_delete_unit_parent(
    --Unit id
    a_unit_id raw,
    --Parent unit id
    a_parent_unit_id raw)
as
    --Row count
    a_row_count number;
    --Affected units
    a_affected_units meta_id_list := meta_id_list();
begin
    p_check_unit_exists(a_unit_id);
    p_check_unit_exists(a_parent_unit_id);
    
    --Searching for the unit parent
    select count(*) into a_row_count
    from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
    
    if a_row_count = 0 then
        raise_application_error(-20051,
                'Specified parent unit was not found (unit_id = '
                || a_unit_id || ', parent_unit_id = '
                || a_parent_unit_id
                || ') (#error_code = 20051)');
    end if;
    
    --Getting affected old units
    if a_parent_unit_id is not null then
        a_affected_units := p_get_unit_parents_children(a_unit_id);
    end if;
        
    --Updating the metadata
    delete from meta_unit_parents
    where unit_id = a_unit_id
    and parent_unit_id = a_parent_unit_id;
   
    --Refreshing new users acl
    for u in (select distinct user_name
        from meta_user_units
        --Getting affected unit users
        where unit_id in (select * from table(a_affected_units))) loop
        p_refresh_user_acl(u.user_name);    
    end loop;
end;

--go